const express = require('express');
const { promisePool } = require('../config/database');
const { createActivitySchema, updateActivitySchema, activityIdSchema, activityStatusSchema } = require('../schema/activities');

const router = express.Router();

// 创建活动接口
router.post('/create', async (req, res) => {
  try {
    // 验证请求数据
    const { error, value } = createActivitySchema.validate(req.body);
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const {
      title,
      subtitle,
      content,
      startDate,
      startTime,
      endDate,
      endTime,
      maxParticipants,
      contactPerson,
      contactPhone,
      location,
      organizer,
      registrationRequired,
      registrationDeadline,
      backgroundColor,
      images,
      createdBy
    } = value;

    // 验证创建人ID是否存在
    const [users] = await promisePool.execute(
      'SELECT id FROM users WHERE id = ?',
      [createdBy]
    );

    if (users.length === 0) {
      return res.status(400).json({
        success: false,
        message: '创建人不存在'
      });
    }

    // 生成随机背景色（如果没有提供）
    let finalBackgroundColor = backgroundColor;
    if (!finalBackgroundColor) {
      // 预定义的好看的背景色列表
      const beautifulColors = [
        '#e8f5e8', // 淡绿色
        '#e6f3ff', // 淡蓝色
        '#fff2e6', // 淡橙色
        '#ffe6e6', // 淡红色
        '#f0e6ff', // 淡紫色
        '#e6ffe6', // 薄荷绿
        '#ffe6f0', // 淡粉色
        '#e6f0ff', // 天蓝色
        '#fff0e6', // 淡黄色
        '#f0ffe6', // 淡青绿
        '#f0f0ff', // 淡紫蓝
        '#ffe6ff'  // 淡紫红
      ];
      // 随机选择一个颜色
      finalBackgroundColor = beautifulColors[Math.floor(Math.random() * beautifulColors.length)];
    }

    // 处理图片数据（从JSON中获取）
    let processedImages = [];
    if (images && images.length > 0) {
      processedImages = images.map((image, index) => ({
        url: image.url,
        name: image.name || `image_${index + 1}`,
        size: image.size || 0,
        type: image.type || 'image/jpeg',
        sortOrder: image.sortOrder || index,
        isCover: image.isCover || (index === 0) // 第一张图片设为封面
      }));
    }

    // 开始数据库事务
    const connection = await promisePool.getConnection();
    await connection.beginTransaction();

    try {
      // 插入活动基本信息
      const [activityResult] = await connection.execute(
        `INSERT INTO activities (
          title, subtitle, content, start_date, start_time, end_date, end_time, max_participants, 
          current_participants, status, contact_person, contact_phone, location, 
          organizer, registration_required, registration_deadline, background_color, created_by, 
          created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0, 'upcoming', ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
        [
          title,
          subtitle || null,
          content || null,
          startDate,
          startTime,
          endDate,
          endTime,
          maxParticipants,
          contactPerson || null,
          contactPhone || null,
          location || null,
          organizer || null,
          registrationRequired,
          registrationDeadline || null,
          finalBackgroundColor,
          createdBy
        ]
      );

      const activityId = activityResult.insertId;

      // 如果有图片，插入图片信息
      if (processedImages && processedImages.length > 0) {
        for (let i = 0; i < processedImages.length; i++) {
          const image = processedImages[i];
          await connection.execute(
            `INSERT INTO activity_images (
              activity_id, image_url, image_name, image_size, image_type, 
              width, height, sort_order, is_cover, created_at, updated_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
            [
              activityId,
              image.url,
              image.name || null,
              image.size || null,
              image.type || null,
              null, // width - 暂时设为null，后续可以添加图片处理功能
              null, // height - 暂时设为null，后续可以添加图片处理功能
              image.sortOrder || i,
              image.isCover || false
            ]
          );
        }
      }

      // 提交事务
      await connection.commit();

      // 查询创建的活动详情
      const [newActivity] = await promisePool.execute(
        `SELECT 
          a.id, a.title, a.subtitle, a.content, a.start_date, a.start_time, a.end_date, a.end_time,
          a.max_participants, a.current_participants, a.status, a.contact_person,
          a.contact_phone, a.location, a.organizer, a.registration_required,
          a.registration_deadline, a.view_count, a.like_count, a.share_count,
          a.background_color, a.created_by, a.created_at, a.updated_at,
          u.name as creator_name
        FROM activities a
        LEFT JOIN users u ON a.created_by = u.id
        WHERE a.id = ? AND a.deleted_at IS NULL`,
        [activityId]
      );

      if (newActivity.length === 0) {
        throw new Error('活动创建失败');
      }

      // 查询活动图片
      const [activityImages] = await promisePool.execute(
        `SELECT id, image_url, image_name, image_size, image_type, width, height, 
         sort_order, is_cover
        FROM activity_images 
        WHERE activity_id = ? 
        ORDER BY sort_order ASC`,
        [activityId]
      );

      const activityData = {
        ...newActivity[0],
        images: activityImages
      };

      res.json({
        success: true,
        message: '活动创建成功',
        data: activityData
      });

    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      // 释放连接
      connection.release();
    }

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 获取活动列表接口
router.get('/list', async (req, res) => {
  try {
    const { 
      page = 1, 
      limit = 10, 
      status, 
      keyword,
      startDate,
      endDate
    } = req.query;

    const offset = (parseInt(page) - 1) * parseInt(limit);
    const limitNum = parseInt(limit);

    // 构建查询条件
    let whereConditions = ['a.deleted_at IS NULL'];
    let queryParams = [];

    if (status) {
      whereConditions.push('a.status = ?');
      queryParams.push(status);
    }

    if (keyword) {
      whereConditions.push('(a.title LIKE ? OR a.subtitle LIKE ? OR a.content LIKE ?)');
      const keywordParam = `%${keyword}%`;
      queryParams.push(keywordParam, keywordParam, keywordParam);
    }

    if (startDate) {
      whereConditions.push('a.start_date >= ?');
      queryParams.push(startDate);
    }

    if (endDate) {
      whereConditions.push('a.end_date <= ?');
      queryParams.push(endDate);
    }

    const whereClause = `WHERE ${whereConditions.join(' AND ')}`;

    // 查询活动列表
    const [activities] = await promisePool.execute(
      `SELECT 
        a.id, a.title, a.subtitle, a.content, a.start_date, a.start_time, a.end_date, a.end_time,
        a.max_participants, a.current_participants, a.status, a.contact_person,
        a.contact_phone, a.location, a.organizer, a.registration_required,
        a.registration_deadline, a.view_count, a.like_count, a.share_count,
        a.background_color, a.created_by, a.created_at, a.updated_at,
        u.name as creator_name,
        COUNT(DISTINCT ap.id) as participant_count,
        COUNT(DISTINCT ac.id) as comment_count
      FROM activities a
      LEFT JOIN users u ON a.created_by = u.id
      LEFT JOIN activity_participants ap ON a.id = ap.activity_id AND ap.status IN ('registered', 'confirmed')
      LEFT JOIN activity_comments ac ON a.id = ac.activity_id AND ac.deleted_at IS NULL
      ${whereClause}
      GROUP BY a.id
      ORDER BY a.created_at DESC
      LIMIT ${limitNum} OFFSET ${offset}`,
      queryParams
    );

    // 查询总数
    const [countResult] = await promisePool.execute(
      `SELECT COUNT(*) as total
      FROM activities a
      ${whereClause}`,
      queryParams
    );

    const total = countResult[0].total;
    const totalPages = Math.ceil(total / limitNum);

    // 为每个活动查询图片
    for (let activity of activities) {
      const [images] = await promisePool.execute(
        `SELECT id, image_url, image_name, image_size, image_type, width, height, 
         sort_order, is_cover
        FROM activity_images 
        WHERE activity_id = ? 
        ORDER BY sort_order ASC`,
        [activity.id]
      );
      
      // 处理图片URL，确保返回正确的访问路径
      const processedImages = images.map(img => {
        let imageUrl = img.image_url;
        
        if (imageUrl) {
          // 如果URL包含临时路径，转换为uploads路径
          if (imageUrl.includes('/_tmp_/')) {
            imageUrl = imageUrl.replace('/_tmp_/', '/uploads/');
          } else if (!imageUrl.startsWith('http') && !imageUrl.startsWith('/uploads/')) {
            // 如果是相对路径，添加uploads前缀
            imageUrl = imageUrl.startsWith('/') ? imageUrl : `/uploads/${imageUrl}`;
          }
          
          // 确保URL以http开头（完整URL）
          if (!imageUrl.startsWith('http')) {
            imageUrl = `http://localhost:3000${imageUrl}`;
          }
        }
        
        return {
          ...img,
          image_url: imageUrl
        };
      });
      
      activity.images = processedImages;
    }

    res.json({
      success: true,
      message: '获取活动列表成功',
      data: {
        activities,
        pagination: {
          currentPage: parseInt(page),
          totalPages,
          totalItems: total,
          itemsPerPage: limitNum
        }
      }
    });

  } catch (error) {
    console.error('获取活动列表失败:', error);
    console.error('错误详情:', {
      message: error.message,
      code: error.code,
      errno: error.errno,
      sqlState: error.sqlState,
      sqlMessage: error.sqlMessage,
      stack: error.stack
    });
    
    // 根据错误类型返回不同的错误信息
    let errorMessage = '服务器内部错误';
    if (error.code === 'ER_NO_SUCH_TABLE') {
      errorMessage = '数据表不存在，请检查数据库配置';
    } else if (error.code === 'ER_ACCESS_DENIED_ERROR') {
      errorMessage = '数据库访问被拒绝，请检查数据库连接配置';
    } else if (error.code === 'ECONNREFUSED') {
      errorMessage = '无法连接到数据库服务器';
    } else if (error.code === 'ER_BAD_DB_ERROR') {
      errorMessage = '数据库不存在，请先创建数据库';
    }
    
    res.status(500).json({
      success: false,
      message: errorMessage,
      error: process.env.NODE_ENV === 'development' ? {
        message: error.message,
        code: error.code,
        stack: error.stack
      } : undefined
    });
  }
});

// 获取活动详情接口
router.get('/detail/:id', async (req, res) => {
  try {
    const { error, value } = activityIdSchema.validate({ id: parseInt(req.params.id) });
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { id } = value;

    // 查询活动详情
    const [activities] = await promisePool.execute(
      `SELECT 
        a.id, a.title, a.subtitle, a.content, a.start_date, a.start_time, a.end_date, a.end_time,
        a.max_participants, a.current_participants, a.status, a.contact_person,
        a.contact_phone, a.location, a.organizer, a.registration_required,
        a.registration_deadline, a.view_count, a.like_count, a.share_count,
        a.background_color, a.created_by, a.created_at, a.updated_at,
        u.name as creator_name, u.phone as creator_phone,
        COUNT(DISTINCT ap.id) as participant_count,
        COUNT(DISTINCT ac.id) as comment_count,
        COUNT(DISTINCT ai.id) as image_count
      FROM activities a
      LEFT JOIN users u ON a.created_by = u.id
      LEFT JOIN activity_participants ap ON a.id = ap.activity_id AND ap.status IN ('registered', 'confirmed')
      LEFT JOIN activity_comments ac ON a.id = ac.activity_id AND ac.deleted_at IS NULL
      LEFT JOIN activity_images ai ON a.id = ai.activity_id
      WHERE a.id = ? AND a.deleted_at IS NULL
      GROUP BY a.id`,
      [id]
    );

    if (activities.length === 0) {
      return res.status(404).json({
        success: false,
        message: '活动不存在'
      });
    }

    // 查询活动图片
    const [images] = await promisePool.execute(
      `SELECT id, image_url, image_name, image_size, image_type, width, height, 
       sort_order, is_cover
      FROM activity_images 
      WHERE activity_id = ? 
      ORDER BY sort_order ASC`,
      [id]
    );

    // 处理图片URL，确保返回正确的访问路径
    const processedImages = images.map(img => {
      let imageUrl = img.image_url;
      
      if (imageUrl) {
        // 如果URL包含临时路径，转换为uploads路径
        if (imageUrl.includes('/_tmp_/')) {
          imageUrl = imageUrl.replace('/_tmp_/', '/uploads/');
        } else if (!imageUrl.startsWith('http') && !imageUrl.startsWith('/uploads/')) {
          // 如果是相对路径，添加uploads前缀
          imageUrl = imageUrl.startsWith('/') ? imageUrl : `/uploads/${imageUrl}`;
        }
        
        // 确保URL以http开头（完整URL）
        if (!imageUrl.startsWith('http')) {
          imageUrl = `http://localhost:3000${imageUrl}`;
        }
      }
      
      return {
        ...img,
        image_url: imageUrl
      };
    });

    const activityData = {
      ...activities[0],
      images: processedImages
    };

    // 增加浏览次数
    await promisePool.execute(
      'UPDATE activities SET view_count = view_count + 1 WHERE id = ?',
      [id]
    );

    res.json({
      success: true,
      message: '获取活动详情成功',
      data: activityData
    });

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 更新活动接口
router.put('/update/:id', async (req, res) => {
  try {
    
    const { error: idError, value: idValue } = activityIdSchema.validate({ id: parseInt(req.params.id) });
    if (idError) {
      return res.status(400).json({
        success: false,
        message: idError.details[0].message
      });
    }

    const { error: dataError, value } = updateActivitySchema.validate(req.body);
    if (dataError) {
      return res.status(400).json({
        success: false,
        message: dataError.details[0].message
      });
    }

    const { id } = idValue;

    // 检查活动是否存在
    const [existingActivities] = await promisePool.execute(
      'SELECT id FROM activities WHERE id = ? AND deleted_at IS NULL',
      [id]
    );

    if (existingActivities.length === 0) {
      return res.status(404).json({
        success: false,
        message: '活动不存在'
      });
    }

    // 开始数据库事务
    const connection = await promisePool.getConnection();
    await connection.beginTransaction();

    try {
      // 构建更新字段
      const updateFields = [];
      const updateValues = [];

      Object.keys(value).forEach(key => {
        if (value[key] !== undefined) {
          switch (key) {
            case 'title':
              updateFields.push('title = ?');
              updateValues.push(value[key]);
              break;
            case 'subtitle':
              updateFields.push('subtitle = ?');
              updateValues.push(value[key]);
              break;
            case 'content':
              updateFields.push('content = ?');
              updateValues.push(value[key]);
              break;
            case 'startDate':
              updateFields.push('start_date = ?');
              updateValues.push(value[key]);
              break;
            case 'startTime':
              updateFields.push('start_time = ?');
              updateValues.push(value[key]);
              break;
            case 'endDate':
              updateFields.push('end_date = ?');
              updateValues.push(value[key]);
              break;
            case 'endTime':
              updateFields.push('end_time = ?');
              updateValues.push(value[key]);
              break;
            case 'maxParticipants':
              updateFields.push('max_participants = ?');
              updateValues.push(value[key]);
              break;
            case 'contactPerson':
              updateFields.push('contact_person = ?');
              updateValues.push(value[key]);
              break;
            case 'contactPhone':
              updateFields.push('contact_phone = ?');
              updateValues.push(value[key]);
              break;
            case 'location':
              updateFields.push('location = ?');
              updateValues.push(value[key]);
              break;
            case 'organizer':
              updateFields.push('organizer = ?');
              updateValues.push(value[key]);
              break;
            case 'registrationRequired':
              updateFields.push('registration_required = ?');
              updateValues.push(value[key]);
              break;
            case 'registrationDeadline':
              updateFields.push('registration_deadline = ?');
              updateValues.push(value[key]);
              break;
            case 'backgroundColor':
              updateFields.push('background_color = ?');
              updateValues.push(value[key]);
              break;
            default:
              // 对于未定义的字段，跳过更新
              break;
          }
        }
      });

      if (updateFields.length > 0) {
        updateFields.push('updated_at = NOW()');
        updateValues.push(id);

        await connection.execute(
          `UPDATE activities SET ${updateFields.join(', ')} WHERE id = ?`,
          updateValues
        );
      }

      // 如果有图片更新，先删除旧图片，再插入新图片
      if (value.images !== undefined) {
        await connection.execute(
          'DELETE FROM activity_images WHERE activity_id = ?',
          [id]
        );

        if (value.images && value.images.length > 0) {
          for (let i = 0; i < value.images.length; i++) {
            const image = value.images[i];
            await connection.execute(
              `INSERT INTO activity_images (
                activity_id, image_url, image_name, image_size, image_type, 
                width, height, sort_order, is_cover, created_at, updated_at
              ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
              [
                id,
                image.url,
                image.name || null,
                image.size || null,
                image.type || null,
                image.width || null,
                image.height || null,
                image.sortOrder || i,
                image.isCover || false
              ]
            );
          }
        }
      }

      // 提交事务
      await connection.commit();

      // 查询更新后的活动详情
      const [updatedActivity] = await promisePool.execute(
        `SELECT 
          a.id, a.title, a.subtitle, a.content, a.start_date, a.start_time, a.end_date, a.end_time,
          a.max_participants, a.current_participants, a.status, a.contact_person,
          a.contact_phone, a.location, a.organizer, a.registration_required,
          a.registration_deadline, a.view_count, a.like_count, a.share_count,
          a.background_color, a.created_by, a.created_at, a.updated_at,
          u.name as creator_name
        FROM activities a
        LEFT JOIN users u ON a.created_by = u.id
        WHERE a.id = ? AND a.deleted_at IS NULL`,
        [id]
      );

      // 查询活动图片
      const [images] = await promisePool.execute(
        `SELECT id, image_url, image_name, image_size, image_type, width, height, 
         sort_order, is_cover
        FROM activity_images 
        WHERE activity_id = ? 
        ORDER BY sort_order ASC`,
        [id]
      );

      // 处理图片URL，确保返回正确的访问路径
      const processedImages = images.map(img => {
        let imageUrl = img.image_url;
        
        if (imageUrl) {
          // 如果URL包含临时路径，转换为uploads路径
          if (imageUrl.includes('/_tmp_/')) {
            imageUrl = imageUrl.replace('/_tmp_/', '/uploads/');
          } else if (!imageUrl.startsWith('http') && !imageUrl.startsWith('/uploads/')) {
            // 如果是相对路径，添加uploads前缀
            imageUrl = imageUrl.startsWith('/') ? imageUrl : `/uploads/${imageUrl}`;
          }
          
          // 确保URL以http开头（完整URL）
          if (!imageUrl.startsWith('http')) {
            imageUrl = `http://localhost:3000${imageUrl}`;
          }
        }
        
        return {
          ...img,
          image_url: imageUrl
        };
      });

      const activityData = {
        ...updatedActivity[0],
        images: processedImages
      };

      res.json({
        success: true,
        message: '活动更新成功',
        data: activityData
      });

    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      // 释放连接
      connection.release();
    }

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 删除活动接口
router.delete('/delete/:id', async (req, res) => {
  try {
    const { error, value } = activityIdSchema.validate({ id: parseInt(req.params.id) });
    if (error) {
      return res.status(400).json({
        success: false,
        message: error.details[0].message
      });
    }

    const { id } = value;

    // 检查活动是否存在
    const [existingActivities] = await promisePool.execute(
      'SELECT id, title FROM activities WHERE id = ? AND deleted_at IS NULL',
      [id]
    );

    if (existingActivities.length === 0) {
      return res.status(404).json({
        success: false,
        message: '活动不存在'
      });
    }

    // 软删除活动
    await promisePool.execute(
      'UPDATE activities SET deleted_at = NOW() WHERE id = ?',
      [id]
    );

    res.json({
      success: true,
      message: '活动删除成功'
    });

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 更新活动状态接口
router.put('/status/:id', async (req, res) => {
  try {
    const { error: idError, value: idValue } = activityIdSchema.validate({ id: parseInt(req.params.id) });
    if (idError) {
      return res.status(400).json({
        success: false,
        message: idError.details[0].message
      });
    }

    const { error: statusError, value: statusValue } = activityStatusSchema.validate(req.body);
    if (statusError) {
      return res.status(400).json({
        success: false,
        message: statusError.details[0].message
      });
    }

    const { id } = idValue;
    const { status } = statusValue;

    // 检查活动是否存在
    const [existingActivities] = await promisePool.execute(
      'SELECT id, title, status FROM activities WHERE id = ? AND deleted_at IS NULL',
      [id]
    );

    if (existingActivities.length === 0) {
      return res.status(404).json({
        success: false,
        message: '活动不存在'
      });
    }

    // 更新活动状态
    await promisePool.execute(
      'UPDATE activities SET status = ?, updated_at = NOW() WHERE id = ?',
      [status, id]
    );

    res.json({
      success: true,
      message: '活动状态更新成功',
      data: {
        id: id,
        status: status
      }
    });

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 检查用户报名状态接口
router.get('/check-registration/:id', async (req, res) => {
  try {
    const { error: idError, value: idValue } = activityIdSchema.validate({ id: parseInt(req.params.id) });
    if (idError) {
      return res.status(400).json({
        success: false,
        message: idError.details[0].message
      });
    }

    const { id } = idValue;
    const { userId } = req.query;

    // 验证用户ID
    if (!userId) {
      return res.status(400).json({
        success: false,
        message: '用户ID不能为空'
      });
    }

    // 检查活动是否存在
    const [activities] = await promisePool.execute(
      'SELECT id, title FROM activities WHERE id = ? AND deleted_at IS NULL',
      [id]
    );

    if (activities.length === 0) {
      return res.status(404).json({
        success: false,
        message: '活动不存在'
      });
    }

    // 检查用户是否已报名
    const [participants] = await promisePool.execute(
      'SELECT id, status, registration_time FROM activity_participants WHERE activity_id = ? AND user_id = ?',
      [id, userId]
    );

    const isRegistered = participants.length > 0 && 
      (participants[0].status === 'registered' || participants[0].status === 'confirmed');

    res.json({
      success: true,
      message: '获取报名状态成功',
      data: {
        isRegistered: isRegistered,
        participationId: isRegistered ? participants[0].id : null,
        registrationTime: isRegistered ? participants[0].registration_time : null,
        status: isRegistered ? participants[0].status : null
      }
    });

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 活动报名接口
router.post('/register/:id', async (req, res) => {
  try {
    const { error: idError, value: idValue } = activityIdSchema.validate({ id: parseInt(req.params.id) });
    if (idError) {
      return res.status(400).json({
        success: false,
        message: idError.details[0].message
      });
    }

    const { id } = idValue;
    const { userId, notes } = req.body;

    // 验证用户ID
    if (!userId) {
      return res.status(400).json({
        success: false,
        message: '用户ID不能为空'
      });
    }

    // 检查活动是否存在且未删除
    const [activities] = await promisePool.execute(
      `SELECT id, title, status, max_participants, current_participants, 
              registration_required, registration_deadline, start_date, start_time
       FROM activities 
       WHERE id = ? AND deleted_at IS NULL`,
      [id]
    );

    if (activities.length === 0) {
      return res.status(404).json({
        success: false,
        message: '活动不存在'
      });
    }

    const activity = activities[0];

    // 检查活动状态
    if (activity.status === 'completed' || activity.status === 'cancelled') {
      return res.status(400).json({
        success: false,
        message: '活动已结束或已取消，无法报名'
      });
    }

    // 检查是否需要报名
    if (!activity.registration_required) {
      return res.status(400).json({
        success: false,
        message: '该活动无需报名'
      });
    }

    // 检查报名截止时间
    if (activity.registration_deadline) {
      const now = new Date();
      const deadline = new Date(activity.registration_deadline);
      if (now > deadline) {
        return res.status(400).json({
          success: false,
          message: '报名已截止'
        });
      }
    }

    // 检查是否已报名
    const [existingParticipants] = await promisePool.execute(
      'SELECT id, status FROM activity_participants WHERE activity_id = ? AND user_id = ?',
      [id, userId]
    );

    let existingParticipant = null;
    if (existingParticipants.length > 0) {
      const participant = existingParticipants[0];
      if (participant.status === 'registered' || participant.status === 'confirmed') {
        return res.status(400).json({
          success: false,
          message: '您已经报名过该活动'
        });
      }
      // 如果状态是 cancelled 或 completed，允许重新报名
      existingParticipant = participant;
    }

    // 检查报名人数是否已满
    if (activity.current_participants >= activity.max_participants) {
      return res.status(400).json({
        success: false,
        message: '活动报名人数已满'
      });
    }

    // 开始数据库事务
    const connection = await promisePool.getConnection();
    await connection.beginTransaction();

    try {
      let participantResult;
      
      if (existingParticipant) {
        // 更新已存在的记录（重新报名）
        await connection.execute(
          `UPDATE activity_participants 
           SET status = 'registered', registration_time = NOW(), notes = ?, updated_at = NOW()
           WHERE id = ?`,
          [notes || null, existingParticipant.id]
        );
        participantResult = { insertId: existingParticipant.id };
      } else {
        // 插入新的报名记录
        const [insertResult] = await connection.execute(
          `INSERT INTO activity_participants (
            activity_id, user_id, status, registration_time, notes, created_at, updated_at
          ) VALUES (?, ?, 'registered', NOW(), ?, NOW(), NOW())`,
          [id, userId, notes || null]
        );
        participantResult = insertResult;
      }

      // 更新活动参与人数
      await connection.execute(
        'UPDATE activities SET current_participants = current_participants + 1, updated_at = NOW() WHERE id = ?',
        [id]
      );

      // 提交事务
      await connection.commit();

      res.json({
        success: true,
        message: '报名成功',
        data: {
          participationId: participantResult.insertId,
          activityId: id,
          userId: userId,
          status: 'registered',
          registrationTime: new Date().toISOString()
        }
      });

    } catch (error) {
      // 回滚事务
      await connection.rollback();
      
      // 处理重复报名错误
      if (error.code === 'ER_DUP_ENTRY' && error.sqlMessage.includes('uk_activity_user')) {
        return res.status(400).json({
          success: false,
          message: '您已经报名过该活动'
        });
      }
      
      throw error;
    } finally {
      // 释放连接
      connection.release();
    }

  } catch (error) {
    // 处理重复报名错误
    if (error.code === 'ER_DUP_ENTRY' && error.sqlMessage.includes('uk_activity_user')) {
      return res.status(400).json({
        success: false,
        message: '您已经报名过该活动'
      });
    }
    
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 取消报名接口
router.post('/cancel-registration/:id', async (req, res) => {
  try {
    const { error: idError, value: idValue } = activityIdSchema.validate({ id: parseInt(req.params.id) });
    if (idError) {
      return res.status(400).json({
        success: false,
        message: idError.details[0].message
      });
    }

    const { id } = idValue;
    const { userId } = req.body;

    // 验证用户ID
    if (!userId) {
      return res.status(400).json({
        success: false,
        message: '用户ID不能为空'
      });
    }

    // 检查活动是否存在且未删除
    const [activities] = await promisePool.execute(
      'SELECT id, title, status FROM activities WHERE id = ? AND deleted_at IS NULL',
      [id]
    );

    if (activities.length === 0) {
      return res.status(404).json({
        success: false,
        message: '活动不存在'
      });
    }

    // 检查用户是否已报名
    const [existingParticipants] = await promisePool.execute(
      'SELECT id, status FROM activity_participants WHERE activity_id = ? AND user_id = ?',
      [id, userId]
    );

    if (existingParticipants.length === 0) {
      return res.status(400).json({
        success: false,
        message: '您还没有报名该活动'
      });
    }

    const participant = existingParticipants[0];

    // 检查报名状态
    if (participant.status === 'cancelled') {
      return res.status(400).json({
        success: false,
        message: '您已经取消过该活动的报名'
      });
    }

    if (participant.status === 'completed') {
      return res.status(400).json({
        success: false,
        message: '活动已完成，无法取消报名'
      });
    }

    // 开始数据库事务
    const connection = await promisePool.getConnection();
    await connection.beginTransaction();

    try {
      // 更新参与状态为已取消
      await connection.execute(
        `UPDATE activity_participants 
         SET status = 'cancelled', cancellation_time = NOW(), updated_at = NOW() 
         WHERE id = ?`,
        [participant.id]
      );

      // 更新活动参与人数
      await connection.execute(
        'UPDATE activities SET current_participants = current_participants - 1, updated_at = NOW() WHERE id = ?',
        [id]
      );

      // 提交事务
      await connection.commit();

      res.json({
        success: true,
        message: '取消报名成功',
        data: {
          participationId: participant.id,
          activityId: id,
          userId: userId,
          status: 'cancelled',
          cancellationTime: new Date().toISOString()
        }
      });

    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      // 释放连接
      connection.release();
    }

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 发布评论接口
router.post('/comment/:id', async (req, res) => {
  try {
    const { error: idError, value: idValue } = activityIdSchema.validate({ id: parseInt(req.params.id) });
    if (idError) {
      return res.status(400).json({
        success: false,
        message: idError.details[0].message
      });
    }

    const { id } = idValue;
    const { userId, content, parentId } = req.body;

    // 验证必要参数
    if (!userId) {
      return res.status(400).json({
        success: false,
        message: '用户ID不能为空'
      });
    }

    if (!content || content.trim() === '') {
      return res.status(400).json({
        success: false,
        message: '评论内容不能为空'
      });
    }

    if (content.length > 200) {
      return res.status(400).json({
        success: false,
        message: '评论内容不能超过200个字符'
      });
    }

    // 检查活动是否存在
    const [activities] = await promisePool.execute(
      'SELECT id, title FROM activities WHERE id = ? AND deleted_at IS NULL',
      [id]
    );

    if (activities.length === 0) {
      return res.status(404).json({
        success: false,
        message: '活动不存在'
      });
    }

    // 如果是回复评论，检查父评论是否存在
    if (parentId) {
      const [parentComments] = await promisePool.execute(
        'SELECT id FROM activity_comments WHERE id = ? AND activity_id = ? AND deleted_at IS NULL',
        [parentId, id]
      );

      if (parentComments.length === 0) {
        return res.status(404).json({
          success: false,
          message: '被回复的评论不存在'
        });
      }
    }

    // 开始数据库事务
    const connection = await promisePool.getConnection();
    await connection.beginTransaction();

    try {
      // 插入评论记录
      const [commentResult] = await connection.execute(
        `INSERT INTO activity_comments (
          activity_id, user_id, parent_id, content, like_count, reply_count, 
          is_approved, is_pinned, created_at, updated_at
        ) VALUES (?, ?, ?, ?, 0, 0, 1, 0, NOW(), NOW())`,
        [
          id,
          userId,
          parentId || null,
          content.trim()
        ]
      );

      const commentId = commentResult.insertId;

      // 如果是回复评论，更新父评论的回复数
      if (parentId) {
        await connection.execute(
          'UPDATE activity_comments SET reply_count = reply_count + 1, updated_at = NOW() WHERE id = ?',
          [parentId]
        );
      }

      // 提交事务
      await connection.commit();

      // 查询新创建的评论详情
      const [newComment] = await promisePool.execute(
        `SELECT 
          ac.id, ac.activity_id, ac.user_id, ac.parent_id, ac.content, 
          ac.like_count, ac.reply_count, ac.is_approved, ac.is_pinned,
          ac.created_at, ac.updated_at,
          u.name as user_name, u.avatar as user_avatar, u.role as user_role
        FROM activity_comments ac
        LEFT JOIN users u ON ac.user_id = u.id
        WHERE ac.id = ?`,
        [commentId]
      );

      // 处理头像URL
      if (newComment.length > 0 && newComment[0].user_avatar) {
        let avatarUrl = newComment[0].user_avatar;
        
        // 如果头像URL不是完整的HTTP URL，则添加服务器基础URL
        if (!avatarUrl.startsWith('http://') && !avatarUrl.startsWith('https://')) {
          if (avatarUrl.startsWith('/')) {
            avatarUrl = `http://localhost:3000${avatarUrl}`;
          } else {
            avatarUrl = `http://localhost:3000/uploads/${avatarUrl}`;
          }
        }
        
        newComment[0].user_avatar = avatarUrl;
      } else {
      }

      res.json({
        success: true,
        message: parentId ? '回复成功' : '评论发布成功',
        data: {
          comment: newComment[0],
          isReply: !!parentId
        }
      });

    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      // 释放连接
      connection.release();
    }

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 获取活动评论列表接口
router.get('/comments/:id', async (req, res) => {
  try {
    const { error: idError, value: idValue } = activityIdSchema.validate({ id: parseInt(req.params.id) });
    if (idError) {
      return res.status(400).json({
        success: false,
        message: idError.details[0].message
      });
    }

    const { id } = idValue;
    const { page = 1, limit = 10 } = req.query;

    const offset = (parseInt(page) - 1) * parseInt(limit);
    const limitNum = parseInt(limit);

    // 检查活动是否存在
    const [activities] = await promisePool.execute(
      'SELECT id, title FROM activities WHERE id = ? AND deleted_at IS NULL',
      [id]
    );

    if (activities.length === 0) {
      return res.status(404).json({
        success: false,
        message: '活动不存在'
      });
    }

    // 查询评论列表（只查询主评论，不包含回复）
    const [comments] = await promisePool.execute(
      `SELECT 
        ac.id, ac.activity_id, ac.user_id, ac.parent_id, ac.content, 
        ac.like_count, ac.reply_count, ac.is_approved, ac.is_pinned,
        ac.created_at, ac.updated_at,
        u.name as user_name, u.avatar as user_avatar, u.role as user_role
      FROM activity_comments ac
      LEFT JOIN users u ON ac.user_id = u.id
      WHERE ac.activity_id = ? AND ac.parent_id IS NULL AND ac.deleted_at IS NULL
      ORDER BY ac.is_pinned DESC, ac.created_at DESC
      LIMIT ${limitNum} OFFSET ${offset}`,
      [id]
    );

    // 查询总数
    const [countResult] = await promisePool.execute(
      `SELECT COUNT(*) as total
      FROM activity_comments 
      WHERE activity_id = ? AND parent_id IS NULL AND deleted_at IS NULL`,
      [id]
    );

    const total = countResult[0].total;
    const totalPages = Math.ceil(total / limitNum);

    // 处理主评论的头像URL
    for (let comment of comments) {
      if (comment.user_avatar) {
        let avatarUrl = comment.user_avatar;
        
        // 如果头像URL不是完整的HTTP URL，则添加服务器基础URL
        if (!avatarUrl.startsWith('http://') && !avatarUrl.startsWith('https://')) {
          if (avatarUrl.startsWith('/')) {
            avatarUrl = `http://localhost:3000${avatarUrl}`;
          } else {
            avatarUrl = `http://localhost:3000/uploads/${avatarUrl}`;
          }
        }
        
        comment.user_avatar = avatarUrl;
      } else {
      }
    }

    // 为每个主评论查询回复
    for (let comment of comments) {
      const [replies] = await promisePool.execute(
        `SELECT 
          ac.id, ac.activity_id, ac.user_id, ac.parent_id, ac.content, 
          ac.like_count, ac.reply_count, ac.is_approved, ac.is_pinned,
          ac.created_at, ac.updated_at,
          u.name as user_name, u.avatar as user_avatar, u.role as user_role
        FROM activity_comments ac
        LEFT JOIN users u ON ac.user_id = u.id
        WHERE ac.parent_id = ? AND ac.deleted_at IS NULL
        ORDER BY ac.created_at ASC`,
        [comment.id]
      );
      
      // 处理回复的头像URL
      for (let reply of replies) {
        if (reply.user_avatar) {
          let avatarUrl = reply.user_avatar;
          
          // 如果头像URL不是完整的HTTP URL，则添加服务器基础URL
          if (!avatarUrl.startsWith('http://') && !avatarUrl.startsWith('https://')) {
            if (avatarUrl.startsWith('/')) {
              avatarUrl = `http://localhost:3000${avatarUrl}`;
            } else {
              avatarUrl = `http://localhost:3000/uploads/${avatarUrl}`;
            }
          }
          
          reply.user_avatar = avatarUrl;
        } else {
        }
      }
      
      comment.replies = replies;
    }

    res.json({
      success: true,
      message: '获取评论列表成功',
      data: {
        comments,
        pagination: {
          currentPage: parseInt(page),
          totalPages,
          totalItems: total,
          itemsPerPage: limitNum
        }
      }
    });

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 点赞评论接口
router.post('/comment/:commentId/like', async (req, res) => {
  try {
    const { commentId } = req.params;
    const { userId } = req.body;

    // 验证参数
    if (!commentId || isNaN(parseInt(commentId))) {
      return res.status(400).json({
        success: false,
        message: '评论ID格式不正确'
      });
    }

    if (!userId) {
      return res.status(400).json({
        success: false,
        message: '用户ID不能为空'
      });
    }

    // 检查评论是否存在
    const [comments] = await promisePool.execute(
      'SELECT id FROM activity_comments WHERE id = ? AND deleted_at IS NULL',
      [commentId]
    );

    if (comments.length === 0) {
      return res.status(404).json({
        success: false,
        message: '评论不存在'
      });
    }

    // 检查是否已经点赞
    const [existingLikes] = await promisePool.execute(
      'SELECT id FROM comment_likes WHERE comment_id = ? AND user_id = ?',
      [commentId, userId]
    );

    if (existingLikes.length > 0) {
      return res.status(400).json({
        success: false,
        message: '您已经点赞过该评论'
      });
    }

    // 开始数据库事务
    const connection = await promisePool.getConnection();
    await connection.beginTransaction();

    try {
      // 插入点赞记录
      await connection.execute(
        'INSERT INTO comment_likes (comment_id, user_id, created_at) VALUES (?, ?, NOW())',
        [commentId, userId]
      );

      // 更新评论点赞数
      await connection.execute(
        'UPDATE activity_comments SET like_count = like_count + 1, updated_at = NOW() WHERE id = ?',
        [commentId]
      );

      // 提交事务
      await connection.commit();

      res.json({
        success: true,
        message: '点赞成功'
      });

    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      // 释放连接
      connection.release();
    }

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 取消点赞评论接口
router.delete('/comment/:commentId/like', async (req, res) => {
  try {
    const { commentId } = req.params;
    const { userId } = req.query;

    // 验证参数
    if (!commentId || isNaN(parseInt(commentId))) {
      return res.status(400).json({
        success: false,
        message: '评论ID格式不正确'
      });
    }

    if (!userId) {
      return res.status(400).json({
        success: false,
        message: '用户ID不能为空'
      });
    }

    // 检查点赞记录是否存在
    const [existingLikes] = await promisePool.execute(
      'SELECT id FROM comment_likes WHERE comment_id = ? AND user_id = ?',
      [commentId, userId]
    );

    if (existingLikes.length === 0) {
      return res.status(400).json({
        success: false,
        message: '您还没有点赞过该评论'
      });
    }

    // 开始数据库事务
    const connection = await promisePool.getConnection();
    await connection.beginTransaction();

    try {
      // 删除点赞记录
      await connection.execute(
        'DELETE FROM comment_likes WHERE comment_id = ? AND user_id = ?',
        [commentId, userId]
      );

      // 更新评论点赞数
      await connection.execute(
        'UPDATE activity_comments SET like_count = like_count - 1, updated_at = NOW() WHERE id = ?',
        [commentId]
      );

      // 提交事务
      await connection.commit();

      res.json({
        success: true,
        message: '取消点赞成功'
      });

    } catch (error) {
      // 回滚事务
      await connection.rollback();
      throw error;
    } finally {
      // 释放连接
      connection.release();
    }

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 手动更新活动状态接口
router.post('/update-status', async (req, res) => {
  try {
    
    // 调用存储过程更新所有活动状态
    await promisePool.execute('CALL sp_batch_update_all_activities()');
    
    // 获取更新结果
    const [result] = await promisePool.execute('SELECT * FROM activity_status_log ORDER BY created_at DESC LIMIT 10');
    
    res.json({
      success: true,
      message: '活动状态更新完成',
      data: {
        recentUpdates: result
      }
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: '更新活动状态失败: ' + error.message
    });
  }
});

// 获取活动状态更新日志接口
router.get('/status-logs', async (req, res) => {
  try {
    const { page = 1, limit = 20 } = req.query;
    const offset = (page - 1) * limit;
    
    // 获取状态变更日志
    const [logs] = await promisePool.execute(`
      SELECT 
        asl.*,
        a.title as activity_title
      FROM activity_status_log asl
      LEFT JOIN activities a ON asl.activity_id = a.id
      ORDER BY asl.created_at DESC
      LIMIT ? OFFSET ?
    `, [parseInt(limit), parseInt(offset)]);
    
    // 获取总数
    const [countResult] = await promisePool.execute('SELECT COUNT(*) as total FROM activity_status_log');
    const total = countResult[0].total;
    
    res.json({
      success: true,
      data: {
        logs,
        pagination: {
          page: parseInt(page),
          limit: parseInt(limit),
          total,
          pages: Math.ceil(total / limit)
        }
      }
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: '获取状态日志失败: ' + error.message
    });
  }
});

// 获取用户参与活动总数接口
router.get('/user-activity-count/:userId', async (req, res) => {
  try {
    const { userId } = req.params;

    // 验证用户ID
    if (!userId || isNaN(parseInt(userId))) {
      return res.status(400).json({
        success: false,
        message: '用户ID格式不正确'
      });
    }

    // 检查用户是否存在
    const [users] = await promisePool.execute(
      'SELECT id, name FROM users WHERE id = ?',
      [userId]
    );

    if (users.length === 0) {
      return res.status(404).json({
        success: false,
        message: '用户不存在'
      });
    }

    // 查询用户可参与的活动总数（包括所有状态，排除已取消）
    const [countResult] = await promisePool.execute(
      `SELECT COUNT(DISTINCT id) as total_count
       FROM  activities
       WHERE status != 'cancelled' AND deleted_at IS NULL`
    );

    const totalCount = countResult[0].total_count;

    // 查询各状态的活动数量统计
    const [statusStats] = await promisePool.execute(
      `SELECT 
         status,
         COUNT(DISTINCT id) as count
       FROM activities
       WHERE status != 'cancelled' AND deleted_at IS NULL
       GROUP BY status`
    );

    // 格式化状态统计
    const statusCounts = {
      registered: 0,
      confirmed: 0,
      completed: 0
    };

    statusStats.forEach(stat => {
      statusCounts[stat.status] = stat.count;
    });

    res.json({
      success: true,
      message: '获取用户参与活动总数成功',
      data: {
        userId: parseInt(userId),
        userName: users[0].name,
        totalCount: totalCount,
        statusCounts: statusCounts
      }
    });

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 获取管理员总发布活动次数接口
router.get('/admin-activity-count/:adminId', async (req, res) => {
  try {
    const { adminId } = req.params;

    // 验证管理员ID
    if (!adminId || isNaN(parseInt(adminId))) {
      return res.status(400).json({
        success: false,
        message: '管理员ID格式不正确'
      });
    }

    // 检查管理员是否存在
    const [adminUsers] = await promisePool.execute(
      'SELECT id, name, role FROM users WHERE id = ? AND role = ?',
      [adminId, 'admin']
    );

    if (adminUsers.length === 0) {
      return res.status(404).json({
        success: false,
        message: '管理员不存在'
      });
    }

    // 查询管理员发布的活动总数
    const [countResult] = await promisePool.execute(
      `SELECT COUNT(*) as total_count
       FROM activities
       WHERE created_by = ? AND deleted_at IS NULL`,
      [adminId]
    );

    const totalCount = countResult[0].total_count;

    // 查询各状态的活动数量统计
    const [statusStats] = await promisePool.execute(
      `SELECT 
         status,
         COUNT(*) as count
       FROM activities
       WHERE created_by = ? AND deleted_at IS NULL
       GROUP BY status`,
      [adminId]
    );

    // 格式化状态统计
    const statusCounts = {
      upcoming: 0,
      active: 0,
      completed: 0,
      cancelled: 0
    };

    statusStats.forEach(stat => {
      statusCounts[stat.status] = stat.count;
    });

    res.json({
      success: true,
      message: '获取管理员活动发布次数成功',
      data: {
        adminId: parseInt(adminId),
        adminName: adminUsers[0].name,
        totalCount: totalCount,
        statusCounts: statusCounts
      }
    });

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 检查事件调度器状态接口
router.get('/event-scheduler-status', async (req, res) => {
  try {
    // 检查事件调度器状态
    const [schedulerStatus] = await promisePool.execute("SHOW VARIABLES LIKE 'event_scheduler'");
    
    // 检查事件是否存在
    const [events] = await promisePool.execute("SHOW EVENTS LIKE 'ev_update_activity_status'");
    
    // 获取最近的状态更新日志
    const [recentLogs] = await promisePool.execute(`
      SELECT * FROM activity_status_log 
      ORDER BY created_at DESC 
      LIMIT 5
    `);
    
    res.json({
      success: true,
      data: {
        eventScheduler: schedulerStatus.length > 0 ? schedulerStatus[0].Value : 'UNKNOWN',
        eventExists: events.length > 0,
        eventDetails: events.length > 0 ? events[0] : null,
        recentLogs
      }
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: '检查事件调度器状态失败: ' + error.message
    });
  }
});

// 获取即将开始或正在进行的活动接口
router.get('/active-activities', async (req, res) => {
  try {
    const { 
      page = 1, 
      limit = 10, 
      keyword
    } = req.query;

    const offset = (parseInt(page) - 1) * parseInt(limit);
    const limitNum = parseInt(limit);

    // 构建查询条件
    let whereConditions = ['a.deleted_at IS NULL', 'a.status IN (?, ?)'];
    let queryParams = ['upcoming', 'active'];

    if (keyword) {
      whereConditions.push('(a.title LIKE ? OR a.subtitle LIKE ? OR a.content LIKE ?)');
      const keywordParam = `%${keyword}%`;
      queryParams.push(keywordParam, keywordParam, keywordParam);
    }

    const whereClause = `WHERE ${whereConditions.join(' AND ')}`;

    // 查询即将开始或正在进行的活动列表
    const [activities] = await promisePool.execute(
      `SELECT 
        a.id, a.title, a.subtitle, a.content, a.start_date, a.start_time, a.end_date, a.end_time,
        a.max_participants, a.current_participants, a.status, a.contact_person,
        a.contact_phone, a.location, a.organizer, a.registration_required,
        a.registration_deadline, a.view_count, a.like_count, a.share_count,
        a.background_color, a.created_by, a.created_at, a.updated_at,
        u.name as creator_name,
        COUNT(DISTINCT ap.id) as participant_count,
        COUNT(DISTINCT ac.id) as comment_count
      FROM activities a
      LEFT JOIN users u ON a.created_by = u.id
      LEFT JOIN activity_participants ap ON a.id = ap.activity_id AND ap.status IN ('registered', 'confirmed')
      LEFT JOIN activity_comments ac ON a.id = ac.activity_id AND ac.deleted_at IS NULL
      ${whereClause}
      GROUP BY a.id
      ORDER BY a.start_date ASC, a.start_time ASC
      LIMIT ${limitNum} OFFSET ${offset}`,
      queryParams
    );

    // 查询总数
    const [countResult] = await promisePool.execute(
      `SELECT COUNT(*) as total
      FROM activities a
      ${whereClause}`,
      queryParams
    );

    const total = countResult[0].total;
    const totalPages = Math.ceil(total / limitNum);

    // 为每个活动查询图片
    for (let activity of activities) {
      const [images] = await promisePool.execute(
        `SELECT id, image_url, image_name, image_size, image_type, width, height, 
         sort_order, is_cover
        FROM activity_images 
        WHERE activity_id = ? 
        ORDER BY sort_order ASC`,
        [activity.id]
      );
      
      // 处理图片URL，确保返回正确的访问路径
      const processedImages = images.map(img => {
        let imageUrl = img.image_url;
        
        if (imageUrl) {
          // 如果URL包含临时路径，转换为uploads路径
          if (imageUrl.includes('/_tmp_/')) {
            imageUrl = imageUrl.replace('/_tmp_/', '/uploads/');
          } else if (!imageUrl.startsWith('http') && !imageUrl.startsWith('/uploads/')) {
            // 如果是相对路径，添加uploads前缀
            imageUrl = imageUrl.startsWith('/') ? imageUrl : `/uploads/${imageUrl}`;
          }
          
          // 确保URL以http开头（完整URL）
          if (!imageUrl.startsWith('http')) {
            imageUrl = `http://localhost:3000${imageUrl}`;
          }
        }
        
        return {
          ...img,
          image_url: imageUrl
        };
      });
      
      activity.images = processedImages;
    }

    res.json({
      success: true,
      message: '获取活动数据成功',
      data: {
        activities,
        pagination: {
          currentPage: parseInt(page),
          totalPages,
          totalItems: total,
          itemsPerPage: limitNum
        }
      }
    });

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

// 用户获取所有活动数据接口
router.get('/user-activities', async (req, res) => {
  try {
    const { 
      page = 1, 
      limit = 10, 
      status, 
      keyword,
      userId
    } = req.query;

    const offset = (parseInt(page) - 1) * parseInt(limit);
    const limitNum = parseInt(limit);

    // 验证用户ID
    if (!userId) {
      return res.status(400).json({
        success: false,
        message: '用户ID不能为空'
      });
    }

    // 构建查询条件
    let whereConditions = ['a.deleted_at IS NULL'];
    let queryParams = [];

    if (status) {
      whereConditions.push('a.status = ?');
      queryParams.push(status);
    }

    if (keyword) {
      whereConditions.push('(a.title LIKE ? OR a.subtitle LIKE ? OR a.content LIKE ?)');
      const keywordParam = `%${keyword}%`;
      queryParams.push(keywordParam, keywordParam, keywordParam);
    }

    const whereClause = `WHERE ${whereConditions.join(' AND ')}`;

    // 查询用户参与的活动列表
    const [activities] = await promisePool.execute(
      `SELECT 
        a.id, a.title, a.subtitle, a.content, a.start_date, a.start_time, a.end_date, a.end_time,
        a.max_participants, a.current_participants, a.status, a.contact_person,
        a.contact_phone, a.location, a.organizer, a.registration_required,
        a.registration_deadline, a.view_count, a.like_count, a.share_count,
        a.background_color, a.created_by, a.created_at, a.updated_at,
        u.name as creator_name,
        ap.status as participation_status,
        ap.registration_time,
        ap.confirmation_time,
        ap.cancellation_time,
        ap.completion_time,
        ap.notes as participation_notes,
        COUNT(DISTINCT ac.id) as comment_count
      FROM activities a
      LEFT JOIN users u ON a.created_by = u.id
      LEFT JOIN activity_participants ap ON a.id = ap.activity_id AND ap.user_id = ?
      LEFT JOIN activity_comments ac ON a.id = ac.activity_id AND ac.deleted_at IS NULL
      ${whereClause}
      GROUP BY a.id, ap.id
      ORDER BY a.created_at DESC
      LIMIT ${limitNum} OFFSET ${offset}`,
      [userId, ...queryParams]
    );

    // 查询总数
    const [countResult] = await promisePool.execute(
      `SELECT COUNT(DISTINCT a.id) as total
      FROM activities a
      LEFT JOIN activity_participants ap ON a.id = ap.activity_id AND ap.user_id = ?
      ${whereClause}`,
      [userId, ...queryParams]
    );

    const total = countResult[0].total;
    const totalPages = Math.ceil(total / limitNum);

    // 为每个活动查询图片
    for (let activity of activities) {
      const [images] = await promisePool.execute(
        `SELECT id, image_url, image_name, image_size, image_type, width, height, 
         sort_order, is_cover
        FROM activity_images 
        WHERE activity_id = ? 
        ORDER BY sort_order ASC`,
        [activity.id]
      );
      
      // 处理图片URL，确保返回正确的访问路径
      const processedImages = images.map(img => {
        let imageUrl = img.image_url;
        
        if (imageUrl) {
          // 如果URL包含临时路径，转换为uploads路径
          if (imageUrl.includes('/_tmp_/')) {
            imageUrl = imageUrl.replace('/_tmp_/', '/uploads/');
          } else if (!imageUrl.startsWith('http') && !imageUrl.startsWith('/uploads/')) {
            // 如果是相对路径，添加uploads前缀
            imageUrl = imageUrl.startsWith('/') ? imageUrl : `/uploads/${imageUrl}`;
          }
          
          // 确保URL以http开头（完整URL）
          if (!imageUrl.startsWith('http')) {
            imageUrl = `http://localhost:3000${imageUrl}`;
          }
        }
        
        return {
          ...img,
          image_url: imageUrl
        };
      });
      
      activity.images = processedImages;
    }

    res.json({
      success: true,
      message: '获取用户活动数据成功',
      data: {
        activities,
        pagination: {
          currentPage: parseInt(page),
          totalPages,
          totalItems: total,
          itemsPerPage: limitNum
        }
      }
    });

  } catch (error) {
    res.status(500).json({
      success: false,
      message: '服务器内部错误'
    });
  }
});

module.exports = router;
